package com.intellekt.ecertws.ws.dao;

import static com.intellekt.ecertws.domain.DomainUtils.parseStringToBigDecimal;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.GregorianCalendar;
import java.util.List;
import java.util.Properties;

import javax.xml.datatype.DatatypeConfigurationException;
import javax.xml.datatype.DatatypeFactory;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;

import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSAddressType;
import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSAuthenticationType;
import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSClauseType;
import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSConsignmentType;
import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSCountryType;
import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSEventType;
import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSExchangedDocumentType;
import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSLocationType;
import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSNoteType;
import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSPackageType;
import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSPartyType;
import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSPersonType;
import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSQualificationType;
import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSReferencedDocumentType;
import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSShippingMarksType;
import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSTemperatureType;
import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSTradeLineItemType;
import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSTransportEquipmentType;
import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSTransportMeansType;
import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSTransportMovementType;
import un.unece.uncefact.data.standard.spscertificate._6.SPSCertificateType;
import un.unece.uncefact.data.standard.unqualifieddatatype._10.MeasureType;
import un.unece.uncefact.data.standard.unqualifieddatatype._10.QuantityType;
import un.unece.uncefact.data.standard.unqualifieddatatype._10.TextType;

import com.intellekt.ecertws.domain.JdbcDao;
import com.intellekt.ecertws.domain.JdbcQueryParameters;
import com.intellekt.ecertws.ws.vo.UserVO;


public class CertDaoImpl extends JdbcDao implements CertDao {

	private static final Logger log = Logger.getLogger(CertDaoImpl.class);
	
	private String qryCertificatesByInfo;
	private String qryCertificatesById;
	private String qryMercanciasPermitidas;
	private String qryUser;
	private JdbcQueryParameters qryItemsById;
	
	private static ParameterizedRowMapper<SPSCertificateType> CERT_MAPPER = new ParameterizedRowMapper<SPSCertificateType>() {
		public SPSCertificateType mapRow(ResultSet rs, int rowNum) throws SQLException {
			SPSCertificateType obj = new SPSCertificateType();
			SPSExchangedDocumentType exdoc = new SPSExchangedDocumentType();
			
			SPSConsignmentType cons = new SPSConsignmentType();
			
			TextType tt = new TextType();
			
			tt.setLanguageID("es");
			//tt.setValue(rs.getString("SPSEXCHANGEDDOCUMENT_NAME"));
			tt.setValue(rs.getString("DESC_MODALIDAD"));
			exdoc.getName().add(tt);
			
			exdoc.setID(rs.getString("SPSEXCHANGEDDOCUMENT_ID"));
			
			tt = new TextType();
			tt.setLanguageID("es");
			tt.setValue(rs.getString("ISSUERSPSPARTY_NAME"));
			exdoc.getDescription().add(tt);
			
			exdoc.setTypeCode(rs.getString("TYPECODE"));
			exdoc.setStatusCode(rs.getString("STATUSCODE"));
			
			
			try {
				GregorianCalendar gc = new GregorianCalendar();
				Date dt = rs.getDate("ISSUEDATETIME");
				gc.setTime(dt);
				exdoc.setIssueDateTime(DatatypeFactory.newInstance().newXMLGregorianCalendar(gc));
			} catch (DatatypeConfigurationException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			

			SPSPartyType partyType = new SPSPartyType();
			partyType.setID("DGSV");
			partyType.setName(rs.getString("ISSUERSPSPARTY_NAME"));
			partyType.setRoleCode("PQ");
			exdoc.setIssuerSPSParty(partyType);
			
			exdoc.getRecipientSPSParty().add(new SPSPartyType());
			
			SPSNoteType note = new SPSNoteType();
			note.setSubject(rs.getString("INCLUDEDSPSNOTE_SUBJECT"));
			note.getContent().add(rs.getString("INCLUDEDSPSNOTE_CONTENT"));
			exdoc.getIncludedSPSNote().add(note);
			
			
			SPSReferencedDocumentType ref = new SPSReferencedDocumentType();
			ref.setTypeCode(rs.getString("TYPECODE"));
			ref.setRelationshipTypeCode(rs.getString("STATUSCODE"));
			ref.setID(rs.getString("SPSEXCHANGEDDOCUMENT_ID"));
			
			try {
				GregorianCalendar gc = new GregorianCalendar();
				Date dt = rs.getDate("ISSUEDATETIME");
				gc.setTime(dt);
				ref.setIssueDateTime(DatatypeFactory.newInstance().newXMLGregorianCalendar(gc));
			} catch (DatatypeConfigurationException e) {
				e.printStackTrace();
			}
			ref.setInformation("");
			exdoc.getReferenceSPSReferencedDocument().add(ref);
			
			
			
			SPSAuthenticationType authType = new SPSAuthenticationType();
			try {
				GregorianCalendar gc = new GregorianCalendar();
				Date dt = rs.getDate("ACTUALDATETIME");
				gc.setTime(dt);
				authType.setActualDateTime(DatatypeFactory.newInstance().newXMLGregorianCalendar(gc));
			} catch (DatatypeConfigurationException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			SPSLocationType locType = new SPSLocationType();
			locType.getName().add(rs.getString("ISSUESPSLOCATION_NAME"));
			authType.setIssueSPSLocation(locType);
			
			
			partyType = new SPSPartyType();
			partyType.setName(rs.getString("PROVIDERSPSPARTY_NAME"));
			
			SPSPersonType person = new SPSPersonType();
			person.setName(rs.getString("SPECIFIEDSPSPERSON_NAME"));
			
			SPSQualificationType qual = new SPSQualificationType();
			qual.getName().add(rs.getString("ATTAINEDSPSQUALIFICATION_NAME"));
			qual.setAbbreviatedName(rs.getString("ATTAINED_ABBREVIATEDNAME"));
			person.getAttainedSPSQualification().add(qual);
			partyType.setSpecifiedSPSPerson(person);
			authType.setProviderSPSParty(partyType);
			
			//exdoc.getSignatorySPSAuthentication().add(authType);
			
			SPSClauseType clause = new SPSClauseType();
			clause.getContent().add(rs.getString("INCLUDEDSPSCLAUSE_CONTENT"));
			authType.getIncludedSPSClause().add(clause);
			
			exdoc.getSignatorySPSAuthentication().add(authType);
			
			/** END OF EXCHANGEDDOCUMENT **/
			
			partyType = new SPSPartyType();
			partyType.setName(rs.getString("CONSIGNOR_NAME"));
			
			SPSAddressType address = new SPSAddressType();
			address.setPostcodeCode(rs.getString("CONSIGNORPOSTCODE"));
			address.setLineOne(rs.getString("CONSIGNORLINEONE"));
			address.setCityName(rs.getString("CONSIGNORCITYNAME"));
			address.setCountryID(rs.getString("CONSIGNORCOUNTRYID"));
			address.setCountryName(rs.getString("CONSIGNORCOUNTRYNAME"));
			address.setCountrySubDivisionID("");
			
			partyType.setSpecifiedSPSAddress(address);
			
			cons.setConsignorSPSParty(partyType);
			
			partyType = new SPSPartyType();
			partyType.setName(rs.getString("CONSIGNEE_NAME"));
			
			address = new SPSAddressType();
			address.setPostcodeCode("");
			address.setLineOne(rs.getString("CONSIGNEE_LINEONE"));
			address.setCityName(rs.getString("CONSIGNEECITYNAME")==null? "":rs.getString("CONSIGNEECITYNAME"));
			address.setCountryID(rs.getString("CONSIGNEECOUNTRYID"));
			address.setCountryName(rs.getString("CONSIGNEECOUNTRYNAME"));
			
			partyType.setSpecifiedSPSAddress(address);
			cons.setConsigneeSPSParty(partyType);
			
			
			SPSCountryType country = new SPSCountryType();
			country.setID(rs.getString("EXPORTSPSCOUNTRY_ID"));
			country.getName().add(rs.getString("EXPORTSPSCOUNTRY_NAME"));
			
			cons.setExportSPSCountry(country);
			
			country = new SPSCountryType();
			country.setID(rs.getString("IMPORTSPSCOUNTRY_ID"));
			country.getName().add(rs.getString("IMPORTSPSCOUNTRY_NAME"));
			
			cons.setImportSPSCountry(country);
			
			locType = new SPSLocationType();
			locType.getName().add(rs.getString("UNLOADINGBASEPORTSPSLOCATION"));
			
			cons.setUnloadingBaseportSPSLocation(locType);
			
			SPSEventType event = new SPSEventType();
			locType = new SPSLocationType();
			locType.getName().add(rs.getString("OCCURRENCESPSLOCATION_NAME"));
			event.setOccurrenceSPSLocation(locType);
			
			cons.setExaminationSPSEvent(event);
			
			SPSTransportMovementType movement = new SPSTransportMovementType();
			SPSTransportMeansType means = new SPSTransportMeansType();
			
			movement.setModeCode(rs.getString("TRANSPORTMOVEMENT_CODE")==null?"":rs.getString("TRANSPORTMOVEMENT_CODE"));
			means.setName(rs.getString("TRANSPORTMOVEMENT_MODECODE"));
			movement.setUsedSPSTransportMeans(means);
			
			cons.getMainCarriageSPSTransportMovement().add(movement);
			
			//rs.getString("LOADINGBASEPORTSPSLOCATION")
			
			obj.setSPSExchangedDocument(exdoc);
			obj.setSPSConsignment(cons);
			
			return obj;
		}
	};

	
	private static ParameterizedRowMapper<SPSTradeLineItemType> ITEM_MAPPER = new ParameterizedRowMapper<SPSTradeLineItemType>() {
		public SPSTradeLineItemType mapRow(ResultSet rs, int rowNum) throws SQLException {
			SPSTradeLineItemType obj = new SPSTradeLineItemType();
			SPSNoteType noteType = new SPSNoteType();
			SPSPackageType packageType = new SPSPackageType();
			QuantityType quantType = new QuantityType();
			MeasureType measureType = new MeasureType();
			SPSShippingMarksType marksType = new SPSShippingMarksType();
			SPSCountryType countryType = new SPSCountryType();
			SPSTransportEquipmentType quip = new SPSTransportEquipmentType();
			SPSTemperatureType temp = new SPSTemperatureType();
			obj.setSequenceNumeric(rs.getString("SEQUENCENUMERIC"));
			obj.getDescription().add(rs.getString("DESCRIPTION"));
			obj.getCommonName().add(rs.getString("COMMONNAME"));
			obj.getScientificName().add(rs.getString("SCIENTIFICNAME"));
			
			
			noteType.setSubject(rs.getString("ADDITIONALINFO_SUBJECT")==null?"":rs.getString("ADDITIONALINFO_SUBJECT"));
			noteType.getContent().add(rs.getString("ADDITIONALINFO_CONTENT"));
			obj.getAdditionalInformationSPSNote().add(noteType);
			

			packageType.setLevelCode(rs.getString("PACKAGE_LEVELCODE"));
			
			try {
				quantType.setValue(parseStringToBigDecimal("0"/*rs.getString("ITEMQUANTITY")*/));
				quantType.setUnitCode(rs.getString("UNITCODE"));
			} catch (ParseException e) {
				e.printStackTrace();
			}
			packageType.setItemQuantity(quantType);
			
			measureType.setValue(/*rs.getString("PACKAGE_NOMINALVOLUMEMEASURE")*/rs.getString("ITEMQUANTITY"));
			packageType.setNominalGrossVolumeMeasure(measureType);
			
			marksType.setMarking(rs.getString("PHYSICALSPSSHIPPINGMARK"));
			packageType.getPhysicalSPSShippingMarks().add(marksType);
			
			temp.setTypeCode("CEL");
			temp.setValueMeasure(rs.getString("DESC_TEMP_TRATAMIENTO"));
			quip.getSettingSPSTemperature().add(temp);
			
			obj.getAssociatedSPSTransportEquipment().add(quip);
			
			obj.getPhysicalSPSPackage().add(packageType);
			
			countryType.setID(rs.getString("ORIGINSPSCOUNTRY_ID"));
			countryType.getName().add(rs.getString("ORIGINSPSCOUNTRY_NAME"));
			
			obj.getOriginSPSCountry().add(countryType);
			
			SPSLocationType loc = new SPSLocationType();
			loc.setID(rs.getString("ID_ORIGIN_LOCATION"));
			loc.getName().add(rs.getString("ORIGIN_LOCATION"));
			obj.getOriginSPSLocation().add(loc);
			
			return obj;
		}
	};

	
	private static ParameterizedRowMapper<UserVO> USER_MAPPER = new ParameterizedRowMapper<UserVO>() {
		public UserVO mapRow(ResultSet rs, int rowNum) throws SQLException {
			
			return new UserVO(rs.getString("USERNAME"),rs.getString("PASSWORD"),true,true,true, (rs.getString("CVE_ESTATUS").equals("A")?true:false));
			
		}
	};
	
	

	@Override
	public List<SPSCertificateType> getCertificatesByInfo(String from, String to, String issueCountry) {
		if(log.isDebugEnabled()){
			log.debug("getCertificatesByInfo");
		}
		String params[] = {from, to, issueCountry};
		
		return jdbcTemplateVU.query(qryCertificatesByInfo, params, CERT_MAPPER);
	}

	
	@Override
	public List<SPSCertificateType> getCertificatesById(String idCert) {
		if(log.isDebugEnabled()){
			log.debug("getCertificatesById");
		}
		String params[] = {idCert};
		return jdbcTemplateVU.query(qryCertificatesById, params, CERT_MAPPER);
	}
	
	
	@Override
	@SuppressWarnings("unchecked")
    public List<SPSTradeLineItemType> getCertificateItems(String id, List<String> mercPermitida) {
        StringBuffer stb = new StringBuffer(qryItemsById.getSqlBase());

        @SuppressWarnings("rawtypes")
        ArrayList parameters = new ArrayList();
        parameters.add(id);
        Properties params = qryItemsById.getOptionalParameters();

        if(mercPermitida.size()>0){
        	stb.append(params.get("idMercancia"));
        	stb.append(" (");
        	for(String obj : mercPermitida){
        		stb.append(obj).append(",");
        	}
        	stb.setLength(stb.length() - 1);
        	stb.append(" )");
        }else{
        	return null;
        }

        Object[] args = parameters.toArray();

        if (log.isDebugEnabled()) {
            log.debug("getRegistros.... ");
        }

        return jdbcTemplateVU.query(stb.toString(), args, ITEM_MAPPER);
    }

	@Override
	public List<String> getMercanciasPermitidas(String usuario) {
		if(log.isDebugEnabled()){
			log.debug("getMercanciasPermitidas");
		}
		ArrayList params = new ArrayList();
		params.add(usuario);
		return jdbcTemplate.queryForList(qryMercanciasPermitidas, params.toArray(), String.class);
	}
	
	
	@Override
	public UserVO getUser(String user) {
		if(log.isDebugEnabled()){
			log.debug("getUser");
		}
		ArrayList params = new ArrayList();
		params.add(user);
		
		return jdbcTemplate.queryForObject(qryUser, params.toArray(), USER_MAPPER);
	}
	
	
	
	

	public void setQryUser(String qryUser) {
		this.qryUser = qryUser;
	}

	public void setQryCertificatesByInfo(String qryCertificatesByInfo) {
		this.qryCertificatesByInfo = qryCertificatesByInfo;
	}
	public void setQryCertificatesById(String qryCertificatesById) {
		this.qryCertificatesById = qryCertificatesById;
	}

	public void setQryItemsById(JdbcQueryParameters qryItemsById) {
		this.qryItemsById = qryItemsById;
	}

	public void setQryMercanciasPermitidas(String qryMercanciasPermitidas) {
		this.qryMercanciasPermitidas = qryMercanciasPermitidas;
	}


	


	
	
}
